package ext.acae2.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ReportExcelGenerateUtil {
	
	private HSSFWorkbook wb = null;
	
	private File excelFile = null; 

	private HSSFSheet currentSheet = null;
	
	public ReportExcelGenerateUtil() {}

	public ReportExcelGenerateUtil(String filePathName) throws IOException {
		POIFSFileSystem fs = null;
		excelFile = new File(filePathName);
		if (excelFile.exists()) {
			fs = new POIFSFileSystem(new FileInputStream(excelFile));
		}
		if (fs != null) {
			wb = new HSSFWorkbook(fs);
			currentSheet = wb.getSheetAt(0);
		}
	}
	
	public ReportExcelGenerateUtil(FileInputStream inputStream) throws IOException {
		if (inputStream != null) {
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			wb = new HSSFWorkbook(fs);
			currentSheet = wb.getSheetAt(0);
		}
	}
	public HSSFSheet getSheet(){
		return currentSheet;
	}
	
	public boolean exists() {
		return wb != null;
	}
	
	public String getParent() {
		return excelFile.getParent();
	}
	
	public String getFileName() {
		return excelFile.getName();
	}
	
	public boolean createNewFile() throws IOException {
		wb = new HSSFWorkbook();
		
		if (excelFile != null) {
			boolean dirResult = excelFile.getParentFile().mkdirs();
			
			if (dirResult || excelFile.getParentFile().exists()) {
				FileOutputStream fileOut = new FileOutputStream(getParent() + File.separator + getFileName());
			    wb.write(fileOut);
			    fileOut.close();
			} else {
				return false;
			}
		}
		return true;
	}
	
	public boolean createNewSheet(String sheetName) throws IOException {
		if (wb == null) return false;
		HSSFSheet sheet = wb.createSheet(sheetName);
		this.currentSheet = sheet;
		
		if (sheet != null) {
			return true;
		} else {
			return false;
		}
	}
	
	public void switchCurrentSheet(String sheetName) {
		if (wb == null) return ;
		HSSFSheet sheet = wb.getSheet(sheetName);
		
		if (sheet != null) {
			this.currentSheet = sheet;
		}
	}

	
	public void switchCurrentSheet(int sheetId) {
		if (wb == null) return ;
		HSSFSheet sheet = wb.getSheetAt(sheetId);
		
		if (sheet != null) {
			this.currentSheet = sheet;
		}
	}
	
	
	public int getSheetRowCount() {
		return this.currentSheet.getPhysicalNumberOfRows();
	}
	
	public boolean isExistSheet(String sheetName) {
		if (wb == null) return false;
		HSSFSheet sheet = wb.getSheet(sheetName);
		
		if (sheet == null) {
			return false;
		}
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setStringValue(int row, int col, String value) {
		if (wb == null) return false;
		HSSFRow hssfRow = currentSheet.getRow(row);
		if(hssfRow == null){
			hssfRow = currentSheet.createRow(row);
		}	
		HSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setNumericValue(int row, int col, double value) {
		if (wb == null) return false;
		
		HSSFRow hssfRow = currentSheet.createRow(row);
		HSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setDateValue(int row, int col, Date value, String fomat) {
		if (wb == null) return false;
		
		HSSFRow hssfRow = currentSheet.createRow(row);
		HSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		
		SimpleDateFormat dateFormat = new java.text.SimpleDateFormat(fomat);
		cell.setCellValue(dateFormat.format(value));
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setBooleanValue(int row, int col, boolean value) {
		if (wb == null) return false;
		
		HSSFRow hssfRow = currentSheet.createRow(row);
		HSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
		cell.setCellValue(value);
		
		return true;
	}
	
	public boolean mergeCells(int rowFrom, int colFrom, int rowTo, int colTo) {
		if (wb == null) return false;
		
		currentSheet.addMergedRegion(new Region(rowFrom,(short)colFrom,rowTo,(short)colTo));
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public String getValue(int row, int col) {
		if (wb == null) return "";
		String value = "";
		
		HSSFRow hssfrow = currentSheet.getRow(row);
		if (hssfrow == null) {
			return "";
		}
		HSSFCell cell = hssfrow.getCell((short)col);
		if(cell == null){
			return "";
		}
		int type = cell.getCellType();
		
		if(type == HSSFCell.CELL_TYPE_STRING){
			value = cell.getStringCellValue();
		}else if (HSSFDateUtil.isCellDateFormatted(cell)) { 
	        double d = cell.getNumericCellValue();   
	        if (d == 0.0) {
	        	value = "";
	        } else {
		        Date date = HSSFDateUtil.getJavaDate(d);    
		        SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy/MM/dd");
		        value = dateFormat.format(date);
	        }
		}else if(type == HSSFCell.CELL_TYPE_NUMERIC){
			int dvalue = (int)cell.getNumericCellValue();
			value = String.valueOf(dvalue);
		}else if(type == HSSFCell.CELL_TYPE_BOOLEAN){
			value = cell.getBooleanCellValue() + "";
		}else if(type == HSSFCell.CELL_TYPE_BLANK){
			value = "";
		}else{
			value = cell.getStringCellValue();
		}

		if (value == null){
			return "";
		}
		
		return value.trim();
	}
	
	public boolean saveChanges(String fileNamePath) throws IOException {
		if (wb == null || excelFile == null) return false;
		
		//FileOutputStream fileOut = new FileOutputStream(getParent() + File.separator + getFileName());
		FileOutputStream fileOut = new FileOutputStream(fileNamePath);
	    wb.write(fileOut);
	    fileOut.close();
		
		return true;
	}
	
	public void downloadExcel(HttpServletResponse response) throws IOException {
		OutputStream os = response.getOutputStream();
		wb.write(os);
		os.flush();
		os.close();
		
		response.setStatus(HttpServletResponse.SC_OK);
		response.flushBuffer();
	}

	public HSSFWorkbook getWb() {
		return wb;
	}

	public void setWb(HSSFWorkbook wb) {
		this.wb = wb;
	}
	
	/**
	 * �����б���ɫ
	 * @param row:int row
	 * @param col:int col
	 * @param value:String 
	 * @param color:short ��ɫ
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueNoFram(int row, int col, String value, short color) {
		if (wb == null) return false;
		HSSFRow hssfRow = currentSheet.getRow(row);
		if(hssfRow == null){
			hssfRow = currentSheet.createRow(row);
		}	
		HSSFCell cell = hssfRow.createCell((short)col);
		HSSFCellStyle style = wb.createCellStyle();
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// ���ñ����������
		style.setFillForegroundColor(color);// ���ñ���ɫ
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		
		return true;
	}
	
	/**
	 * �����б߿�
	 * @param row:int row
	 * @param col:int col
	 * @param value:String
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueBorder(int row, int col, String value) {
		if (wb == null) return false;
		HSSFRow hssfRow = currentSheet.getRow(row);
		if(hssfRow == null){
			hssfRow = currentSheet.createRow(row);
		}	
		HSSFCell cell = hssfRow.createCell((short)col);
		HSSFCellStyle style = wb.createCellStyle();
		style.setWrapText(true);  
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// �±߿�
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// �ϱ߿�
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// ��߿�
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// �ұ߿�
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new HSSFRichTextString(value));
		return true;
	}
	/**
	 * �����б߿�
	 * @param row:int row
	 * @param col:int col
	 * @param value:String
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueBorder1(int row, int col, String value) {
		if (wb == null) return false;
		HSSFRow hssfRow = currentSheet.getRow(row);
		if(hssfRow == null){
			System.out.println("row:"+row+", is null");
			hssfRow = currentSheet.createRow(row);
		}
		HSSFCell cell = hssfRow.createCell((short)col);
		HSSFCellStyle style = wb.createCellStyle();
		//style.setWrapText(true);  
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// �±߿�
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// �ϱ߿�
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// ��߿�
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// �ұ߿�
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new HSSFRichTextString(value));
		currentSheet.autoSizeColumn(col);
		return true;
	}
}
